{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import dask.dataframe as dd\n",
    "import dask.distributed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "client = dask.distributed.Client()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "trips = dd.read_parquet('/bigdata/all_trips.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['dropoff_datetime', 'dropoff_latitude', 'dropoff_location_id',\n",
       "       'dropoff_longitude', 'ehail_fee', 'extra', 'fare_amount',\n",
       "       'improvement_surcharge', 'mta_tax', 'passenger_count', 'payment_type',\n",
       "       'pickup_datetime', 'pickup_latitude', 'pickup_location_id',\n",
       "       'pickup_longitude', 'rate_code_id', 'store_and_fwd_flag', 'tip_amount',\n",
       "       'tolls_amount', 'total_amount', 'trip_distance', 'trip_type',\n",
       "       'vendor_id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trips.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import uuid, sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "engine = sqlalchemy.create_engine(open('/home/shekhar/.sqlconninfo').read())\n",
    "conn = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "uu = uuid.uuid1().hex\n",
    "tableID = 'uu_{}'.format(uu)\n",
    "tableIDLoc = 'uuloc_{}'.format(uu)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'a84ed8622f4711e79d2d902b3437c844'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "uu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# trips1 = trips.get_partition(1).compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# trips1499 = trips.get_partition(1499).compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# trips1 = trips1.append(trips1499)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "trips1 = trips.get_partition(1).compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "trips1 = trips1.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "trips2 = trips1[['pickup_longitude', 'pickup_latitude', 'pickup_location_id']]\n",
    "trips2.columns = ['lon', 'lat', 'locid']\n",
    "trips2.to_sql(tableID, engine, index_label='trip_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>lon</th>\n",
       "      <th>lat</th>\n",
       "      <th>locid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-73.9706</td>\n",
       "      <td>40.7586</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-73.9994</td>\n",
       "      <td>40.7605</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-73.9999</td>\n",
       "      <td>40.7320</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-73.9793</td>\n",
       "      <td>40.7635</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-74.0047</td>\n",
       "      <td>40.7204</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       lon      lat  locid\n",
       "0 -73.9706  40.7586    NaN\n",
       "1 -73.9994  40.7605    NaN\n",
       "2 -73.9999  40.7320    NaN\n",
       "3 -73.9793  40.7635    NaN\n",
       "4 -74.0047  40.7204    NaN"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trips2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1930590, 23)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trips1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x7f0826d38080>"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.execute('''CREATE UNLOGGED TABLE {} AS\n",
    "SELECT\n",
    "  trip_id,\n",
    "  ST_SetSRID(ST_MakePoint(lon, lat), 4326) as loc\n",
    "FROM {}\n",
    "WHERE locid IS NULL\n",
    ";\n",
    "CREATE INDEX on {} USING GIST(loc);\n",
    "'''.format(tableIDLoc, tableID, tableIDLoc))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "df1 = pd.read_sql('''SELECT t.trip_id, n.gid as census_tract_id\n",
    "FROM {} AS t, nyct2010 AS n\n",
    "WHERE ST_Within(t.loc, n.geom) ORDER BY t.trip_id;'''.format(tableIDLoc), engine)\n",
    "df2 = pd.read_sql('''SELECT t.trip_id, n.gid as taxi_zone_id\n",
    "FROM {} AS t, taxi_zones AS n\n",
    "WHERE ST_Within(t.loc, n.geom) ORDER BY t.trip_id;'''.format(tableIDLoc), engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dropoff_datetime</th>\n",
       "      <th>dropoff_latitude</th>\n",
       "      <th>dropoff_location_id</th>\n",
       "      <th>dropoff_longitude</th>\n",
       "      <th>ehail_fee</th>\n",
       "      <th>extra</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>...</th>\n",
       "      <th>pickup_location_id</th>\n",
       "      <th>pickup_longitude</th>\n",
       "      <th>rate_code_id</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>trip_distance</th>\n",
       "      <th>trip_type</th>\n",
       "      <th>vendor_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-12-26 15:04:45</td>\n",
       "      <td>NaN</td>\n",
       "      <td>144.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>186.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.30</td>\n",
       "      <td>3.10</td>\n",
       "      <td>yellow</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-12-26 15:00:49</td>\n",
       "      <td>NaN</td>\n",
       "      <td>239.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>141.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>2.82</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14.12</td>\n",
       "      <td>2.06</td>\n",
       "      <td>yellow</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-12-26 15:03:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>262.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>79.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.80</td>\n",
       "      <td>4.26</td>\n",
       "      <td>yellow</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-12-26 14:50:33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>234.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>234.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.30</td>\n",
       "      <td>0.30</td>\n",
       "      <td>yellow</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-12-26 14:58:17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>163.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>170.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>1.76</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.56</td>\n",
       "      <td>1.29</td>\n",
       "      <td>yellow</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     dropoff_datetime  dropoff_latitude  dropoff_location_id  \\\n",
       "0 2016-12-26 15:04:45               NaN                144.0   \n",
       "1 2016-12-26 15:00:49               NaN                239.0   \n",
       "2 2016-12-26 15:03:00               NaN                262.0   \n",
       "3 2016-12-26 14:50:33               NaN                234.0   \n",
       "4 2016-12-26 14:58:17               NaN                163.0   \n",
       "\n",
       "   dropoff_longitude  ehail_fee  extra  fare_amount  improvement_surcharge  \\\n",
       "0                NaN        NaN    0.0         13.5                    0.3   \n",
       "1                NaN        NaN    0.0         10.5                    0.3   \n",
       "2                NaN        NaN    0.0         15.0                    0.3   \n",
       "3                NaN        NaN    0.0          3.5                    0.3   \n",
       "4                NaN        NaN    0.0          8.0                    0.3   \n",
       "\n",
       "   mta_tax  passenger_count    ...     pickup_location_id pickup_longitude  \\\n",
       "0      0.5                1    ...                  186.0              NaN   \n",
       "1      0.5                1    ...                  141.0              NaN   \n",
       "2      0.5                2    ...                   79.0              NaN   \n",
       "3      0.5                1    ...                  234.0              NaN   \n",
       "4      0.5                1    ...                  170.0              NaN   \n",
       "\n",
       "   rate_code_id  store_and_fwd_flag  tip_amount  tolls_amount total_amount  \\\n",
       "0             1                   N        1.00           0.0        15.30   \n",
       "1             1                   N        2.82           0.0        14.12   \n",
       "2             1                   N        0.00           0.0        15.80   \n",
       "3             1                   N        0.00           0.0         4.30   \n",
       "4             1                   N        1.76           0.0        10.56   \n",
       "\n",
       "   trip_distance  trip_type  vendor_id  \n",
       "0           3.10     yellow          1  \n",
       "1           2.06     yellow          2  \n",
       "2           4.26     yellow          2  \n",
       "3           0.30     yellow          1  \n",
       "4           1.29     yellow          2  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trips1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>lon</th>\n",
       "      <th>lat</th>\n",
       "      <th>locid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>186.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>141.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>79.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>234.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>170.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   lon  lat  locid\n",
       "0  NaN  NaN  186.0\n",
       "1  NaN  NaN  141.0\n",
       "2  NaN  NaN   79.0\n",
       "3  NaN  NaN  234.0\n",
       "4  NaN  NaN  170.0"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trips2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>census_tract_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trip_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [census_tract_id]\n",
       "Index: []"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.set_index('trip_id').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>taxi_zone_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trip_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [taxi_zone_id]\n",
       "Index: []"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.set_index('trip_id').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "zz = (trips1.dropna(axis=1, how='all').merge(\n",
    "        df1.set_index('trip_id'), left_index=True, right_index=True, how='left', sort=True)).merge(\n",
    "        df2.set_index('trip_id'), left_index=True, right_index=True, how='left', sort=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dropoff_datetime</th>\n",
       "      <th>dropoff_location_id</th>\n",
       "      <th>extra</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>pickup_datetime</th>\n",
       "      <th>pickup_location_id</th>\n",
       "      <th>rate_code_id</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>trip_distance</th>\n",
       "      <th>trip_type</th>\n",
       "      <th>pickup_ct_id</th>\n",
       "      <th>pickup_tz_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-12-26 15:04:45</td>\n",
       "      <td>144.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:26</td>\n",
       "      <td>186.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.30</td>\n",
       "      <td>3.10</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-12-26 15:00:49</td>\n",
       "      <td>239.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:26</td>\n",
       "      <td>141.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2.82</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14.12</td>\n",
       "      <td>2.06</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-12-26 15:03:00</td>\n",
       "      <td>262.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-12-26 14:48:26</td>\n",
       "      <td>79.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.80</td>\n",
       "      <td>4.26</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-12-26 14:50:33</td>\n",
       "      <td>234.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:27</td>\n",
       "      <td>234.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.30</td>\n",
       "      <td>0.30</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-12-26 14:58:17</td>\n",
       "      <td>163.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:27</td>\n",
       "      <td>170.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.76</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.56</td>\n",
       "      <td>1.29</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2016-12-26 15:01:22</td>\n",
       "      <td>230.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:27</td>\n",
       "      <td>164.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.96</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.76</td>\n",
       "      <td>0.88</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2016-12-26 15:10:13</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:27</td>\n",
       "      <td>230.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19.30</td>\n",
       "      <td>4.64</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2016-12-26 14:53:33</td>\n",
       "      <td>160.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:28</td>\n",
       "      <td>82.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.80</td>\n",
       "      <td>0.70</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2016-12-26 14:55:10</td>\n",
       "      <td>236.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:28</td>\n",
       "      <td>237.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.97</td>\n",
       "      <td>1.20</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2016-12-26 15:00:17</td>\n",
       "      <td>162.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:28</td>\n",
       "      <td>237.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.30</td>\n",
       "      <td>1.40</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2016-12-26 14:51:53</td>\n",
       "      <td>43.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-12-26 14:48:28</td>\n",
       "      <td>48.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.74</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.54</td>\n",
       "      <td>0.92</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2016-12-26 14:51:04</td>\n",
       "      <td>140.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-12-26 14:48:28</td>\n",
       "      <td>141.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.80</td>\n",
       "      <td>0.33</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2016-12-26 15:01:38</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:29</td>\n",
       "      <td>239.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.30</td>\n",
       "      <td>2.00</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2016-12-26 14:51:58</td>\n",
       "      <td>234.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:29</td>\n",
       "      <td>90.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.30</td>\n",
       "      <td>0.50</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2016-12-26 14:59:43</td>\n",
       "      <td>237.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:29</td>\n",
       "      <td>236.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2.70</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.50</td>\n",
       "      <td>1.80</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2016-12-26 15:01:57</td>\n",
       "      <td>161.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-12-26 14:48:29</td>\n",
       "      <td>233.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.95</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.75</td>\n",
       "      <td>0.40</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2016-12-26 15:03:36</td>\n",
       "      <td>161.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:29</td>\n",
       "      <td>236.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.80</td>\n",
       "      <td>1.80</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2016-12-26 14:53:24</td>\n",
       "      <td>237.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-12-26 14:48:29</td>\n",
       "      <td>237.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.96</td>\n",
       "      <td>0.53</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2016-12-26 14:54:31</td>\n",
       "      <td>237.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:29</td>\n",
       "      <td>142.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.30</td>\n",
       "      <td>1.28</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2016-12-26 15:03:06</td>\n",
       "      <td>161.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-12-26 14:48:29</td>\n",
       "      <td>230.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2.16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.96</td>\n",
       "      <td>1.10</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2016-12-26 14:50:29</td>\n",
       "      <td>236.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:30</td>\n",
       "      <td>237.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.35</td>\n",
       "      <td>0.80</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2016-12-26 14:55:16</td>\n",
       "      <td>170.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:30</td>\n",
       "      <td>137.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.80</td>\n",
       "      <td>0.70</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2016-12-26 14:59:22</td>\n",
       "      <td>263.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>4</td>\n",
       "      <td>2016-12-26 14:48:30</td>\n",
       "      <td>239.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.55</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.85</td>\n",
       "      <td>2.00</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2016-12-26 14:53:12</td>\n",
       "      <td>142.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5</td>\n",
       "      <td>2016-12-26 14:48:30</td>\n",
       "      <td>239.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.26</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.56</td>\n",
       "      <td>0.73</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2016-12-26 15:00:26</td>\n",
       "      <td>237.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:30</td>\n",
       "      <td>164.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.30</td>\n",
       "      <td>2.08</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2016-12-26 14:58:26</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-12-26 14:48:30</td>\n",
       "      <td>142.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.86</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.16</td>\n",
       "      <td>1.81</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2016-12-26 15:01:41</td>\n",
       "      <td>186.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-12-26 14:48:30</td>\n",
       "      <td>48.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.30</td>\n",
       "      <td>1.01</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2016-12-26 14:49:25</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-26 14:48:30</td>\n",
       "      <td>186.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.80</td>\n",
       "      <td>0.34</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2016-12-26 15:04:55</td>\n",
       "      <td>164.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-12-26 14:48:30</td>\n",
       "      <td>163.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.80</td>\n",
       "      <td>1.54</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2016-12-26 14:52:37</td>\n",
       "      <td>261.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>4</td>\n",
       "      <td>2016-12-26 14:48:30</td>\n",
       "      <td>231.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.32</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.62</td>\n",
       "      <td>0.59</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930560</th>\n",
       "      <td>2017-01-01 00:21:18</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>31.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:34</td>\n",
       "      <td>138.0</td>\n",
       "      <td>1</td>\n",
       "      <td>9.80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>42.60</td>\n",
       "      <td>10.70</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930561</th>\n",
       "      <td>2017-01-01 00:16:59</td>\n",
       "      <td>226.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-12-31 23:59:36</td>\n",
       "      <td>231.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22.30</td>\n",
       "      <td>6.50</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930562</th>\n",
       "      <td>2017-01-01 00:09:43</td>\n",
       "      <td>119.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>12.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:37</td>\n",
       "      <td>236.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.80</td>\n",
       "      <td>3.90</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930563</th>\n",
       "      <td>2017-01-01 00:14:53</td>\n",
       "      <td>42.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:37</td>\n",
       "      <td>213.0</td>\n",
       "      <td>1</td>\n",
       "      <td>14.06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36.36</td>\n",
       "      <td>6.80</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930564</th>\n",
       "      <td>2017-01-01 00:16:36</td>\n",
       "      <td>42.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:37</td>\n",
       "      <td>87.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>33.30</td>\n",
       "      <td>11.61</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930565</th>\n",
       "      <td>2017-01-01 00:14:31</td>\n",
       "      <td>48.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:37</td>\n",
       "      <td>234.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.84</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14.14</td>\n",
       "      <td>1.97</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930566</th>\n",
       "      <td>2017-01-01 00:18:39</td>\n",
       "      <td>209.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>24.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:38</td>\n",
       "      <td>43.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25.80</td>\n",
       "      <td>7.53</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930567</th>\n",
       "      <td>2017-01-01 00:35:13</td>\n",
       "      <td>233.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>23.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:39</td>\n",
       "      <td>68.0</td>\n",
       "      <td>1</td>\n",
       "      <td>4.86</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.16</td>\n",
       "      <td>4.23</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930568</th>\n",
       "      <td>2017-01-01 00:23:15</td>\n",
       "      <td>186.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>14.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:42</td>\n",
       "      <td>161.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3.15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.95</td>\n",
       "      <td>1.50</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930569</th>\n",
       "      <td>2017-01-01 00:02:43</td>\n",
       "      <td>170.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:42</td>\n",
       "      <td>162.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.30</td>\n",
       "      <td>0.40</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930570</th>\n",
       "      <td>2017-01-01 00:11:29</td>\n",
       "      <td>170.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:42</td>\n",
       "      <td>107.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.30</td>\n",
       "      <td>1.57</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930571</th>\n",
       "      <td>2017-01-01 00:14:59</td>\n",
       "      <td>164.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>10.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:43</td>\n",
       "      <td>79.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.80</td>\n",
       "      <td>1.50</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930572</th>\n",
       "      <td>2017-01-01 00:04:11</td>\n",
       "      <td>125.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:44</td>\n",
       "      <td>68.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.30</td>\n",
       "      <td>1.40</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930573</th>\n",
       "      <td>2017-01-01 00:15:56</td>\n",
       "      <td>256.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>18.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:44</td>\n",
       "      <td>40.0</td>\n",
       "      <td>1</td>\n",
       "      <td>4.95</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.75</td>\n",
       "      <td>5.30</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930574</th>\n",
       "      <td>2017-01-01 00:10:47</td>\n",
       "      <td>234.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>8.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>4</td>\n",
       "      <td>2016-12-31 23:59:44</td>\n",
       "      <td>234.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2.94</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.74</td>\n",
       "      <td>1.42</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930575</th>\n",
       "      <td>2017-01-01 00:00:54</td>\n",
       "      <td>237.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:45</td>\n",
       "      <td>237.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.80</td>\n",
       "      <td>0.04</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930576</th>\n",
       "      <td>2017-01-01 00:13:23</td>\n",
       "      <td>141.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>16.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:46</td>\n",
       "      <td>79.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.80</td>\n",
       "      <td>4.70</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930577</th>\n",
       "      <td>2017-01-01 00:16:46</td>\n",
       "      <td>107.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:46</td>\n",
       "      <td>75.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3.86</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23.16</td>\n",
       "      <td>5.13</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930578</th>\n",
       "      <td>2017-01-01 00:13:47</td>\n",
       "      <td>243.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>6</td>\n",
       "      <td>2016-12-31 23:59:46</td>\n",
       "      <td>262.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3.25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.55</td>\n",
       "      <td>6.56</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930579</th>\n",
       "      <td>2017-01-01 00:04:17</td>\n",
       "      <td>238.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:48</td>\n",
       "      <td>75.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.30</td>\n",
       "      <td>1.10</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930580</th>\n",
       "      <td>2017-01-01 00:14:07</td>\n",
       "      <td>168.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>27.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-12-31 23:59:50</td>\n",
       "      <td>209.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.30</td>\n",
       "      <td>9.50</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930581</th>\n",
       "      <td>2017-01-01 00:24:50</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>44.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:53</td>\n",
       "      <td>132.0</td>\n",
       "      <td>1</td>\n",
       "      <td>9.05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>54.35</td>\n",
       "      <td>15.90</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930582</th>\n",
       "      <td>2017-01-01 00:07:02</td>\n",
       "      <td>234.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:53</td>\n",
       "      <td>234.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.30</td>\n",
       "      <td>0.22</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930583</th>\n",
       "      <td>2017-01-01 00:30:21</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>24.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:53</td>\n",
       "      <td>144.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25.80</td>\n",
       "      <td>6.69</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930584</th>\n",
       "      <td>2017-01-01 00:06:44</td>\n",
       "      <td>223.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>10.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:53</td>\n",
       "      <td>138.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.80</td>\n",
       "      <td>3.18</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930585</th>\n",
       "      <td>2017-01-01 00:19:41</td>\n",
       "      <td>94.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>31.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:54</td>\n",
       "      <td>68.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>32.80</td>\n",
       "      <td>11.10</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930586</th>\n",
       "      <td>2017-01-01 00:04:29</td>\n",
       "      <td>42.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:54</td>\n",
       "      <td>166.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.30</td>\n",
       "      <td>1.14</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930587</th>\n",
       "      <td>2017-01-01 00:09:26</td>\n",
       "      <td>186.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>7.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-12-31 23:59:54</td>\n",
       "      <td>68.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.80</td>\n",
       "      <td>0.97</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930588</th>\n",
       "      <td>2017-01-01 00:09:44</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:54</td>\n",
       "      <td>236.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.30</td>\n",
       "      <td>2.42</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930589</th>\n",
       "      <td>2017-01-01 00:03:50</td>\n",
       "      <td>209.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 23:59:58</td>\n",
       "      <td>144.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.30</td>\n",
       "      <td>0.70</td>\n",
       "      <td>yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1930590 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           dropoff_datetime  dropoff_location_id  extra  fare_amount  \\\n",
       "0       2016-12-26 15:04:45                144.0    0.0         13.5   \n",
       "1       2016-12-26 15:00:49                239.0    0.0         10.5   \n",
       "2       2016-12-26 15:03:00                262.0    0.0         15.0   \n",
       "3       2016-12-26 14:50:33                234.0    0.0          3.5   \n",
       "4       2016-12-26 14:58:17                163.0    0.0          8.0   \n",
       "5       2016-12-26 15:01:22                230.0    0.0          9.0   \n",
       "6       2016-12-26 15:10:13                 13.0    0.0         18.5   \n",
       "7       2016-12-26 14:53:33                160.0    0.0          5.0   \n",
       "8       2016-12-26 14:55:10                236.0    0.0          7.0   \n",
       "9       2016-12-26 15:00:17                162.0    0.0          9.5   \n",
       "10      2016-12-26 14:51:53                 43.0    0.0          5.0   \n",
       "11      2016-12-26 14:51:04                140.0    0.0          4.0   \n",
       "12      2016-12-26 15:01:38                 50.0    0.0         10.5   \n",
       "13      2016-12-26 14:51:58                234.0    0.0          4.5   \n",
       "14      2016-12-26 14:59:43                237.0    0.0         10.0   \n",
       "15      2016-12-26 15:01:57                161.0    0.0          9.0   \n",
       "16      2016-12-26 15:03:36                161.0    0.0         11.0   \n",
       "17      2016-12-26 14:53:24                237.0    0.0          5.0   \n",
       "18      2016-12-26 14:54:31                237.0    0.0          6.5   \n",
       "19      2016-12-26 15:03:06                161.0    0.0         10.0   \n",
       "20      2016-12-26 14:50:29                236.0    0.0          4.5   \n",
       "21      2016-12-26 14:55:16                170.0    0.0          6.0   \n",
       "22      2016-12-26 14:59:22                263.0    0.0          9.5   \n",
       "23      2016-12-26 14:53:12                142.0    0.0          5.5   \n",
       "24      2016-12-26 15:00:26                237.0    0.0         10.5   \n",
       "25      2016-12-26 14:58:26                229.0    0.0          8.5   \n",
       "26      2016-12-26 15:01:41                186.0    0.0          9.5   \n",
       "27      2016-12-26 14:49:25                 90.0    0.0          3.0   \n",
       "28      2016-12-26 15:04:55                164.0    0.0         11.0   \n",
       "29      2016-12-26 14:52:37                261.0    0.0          4.5   \n",
       "...                     ...                  ...    ...          ...   \n",
       "1930560 2017-01-01 00:21:18                 25.0    0.5         31.5   \n",
       "1930561 2017-01-01 00:16:59                226.0    0.5         21.0   \n",
       "1930562 2017-01-01 00:09:43                119.0    0.5         12.5   \n",
       "1930563 2017-01-01 00:14:53                 42.0    0.5         21.0   \n",
       "1930564 2017-01-01 00:16:36                 42.0    0.5         32.0   \n",
       "1930565 2017-01-01 00:14:31                 48.0    0.5         11.0   \n",
       "1930566 2017-01-01 00:18:39                209.0    0.5         24.5   \n",
       "1930567 2017-01-01 00:35:13                233.0    0.5         23.0   \n",
       "1930568 2017-01-01 00:23:15                186.0    0.5         14.5   \n",
       "1930569 2017-01-01 00:02:43                170.0    0.5          4.0   \n",
       "1930570 2017-01-01 00:11:29                170.0    0.5          9.0   \n",
       "1930571 2017-01-01 00:14:59                164.0    0.5         10.5   \n",
       "1930572 2017-01-01 00:04:11                125.0    0.5          6.0   \n",
       "1930573 2017-01-01 00:15:56                256.0    0.5         18.5   \n",
       "1930574 2017-01-01 00:10:47                234.0    0.5          8.5   \n",
       "1930575 2017-01-01 00:00:54                237.0    0.5          2.5   \n",
       "1930576 2017-01-01 00:13:23                141.0    0.5         16.5   \n",
       "1930577 2017-01-01 00:16:46                107.0    0.5         18.0   \n",
       "1930578 2017-01-01 00:13:47                243.0    0.5         20.0   \n",
       "1930579 2017-01-01 00:04:17                238.0    0.5          6.0   \n",
       "1930580 2017-01-01 00:14:07                168.0    0.5         27.0   \n",
       "1930581 2017-01-01 00:24:50                  7.0    0.5         44.0   \n",
       "1930582 2017-01-01 00:07:02                234.0    0.5          6.0   \n",
       "1930583 2017-01-01 00:30:21                 76.0    0.5         24.5   \n",
       "1930584 2017-01-01 00:06:44                223.0    0.5         10.5   \n",
       "1930585 2017-01-01 00:19:41                 94.0    0.5         31.5   \n",
       "1930586 2017-01-01 00:04:29                 42.0    0.5          6.0   \n",
       "1930587 2017-01-01 00:09:26                186.0    0.5          7.5   \n",
       "1930588 2017-01-01 00:09:44                229.0    0.5         10.0   \n",
       "1930589 2017-01-01 00:03:50                209.0    0.5          5.0   \n",
       "\n",
       "         improvement_surcharge  mta_tax  passenger_count     pickup_datetime  \\\n",
       "0                          0.3      0.5                1 2016-12-26 14:48:26   \n",
       "1                          0.3      0.5                1 2016-12-26 14:48:26   \n",
       "2                          0.3      0.5                2 2016-12-26 14:48:26   \n",
       "3                          0.3      0.5                1 2016-12-26 14:48:27   \n",
       "4                          0.3      0.5                1 2016-12-26 14:48:27   \n",
       "5                          0.3      0.5                1 2016-12-26 14:48:27   \n",
       "6                          0.3      0.5                1 2016-12-26 14:48:27   \n",
       "7                          0.3      0.5                1 2016-12-26 14:48:28   \n",
       "8                          0.3      0.5                1 2016-12-26 14:48:28   \n",
       "9                          0.3      0.5                1 2016-12-26 14:48:28   \n",
       "10                         0.3      0.5                2 2016-12-26 14:48:28   \n",
       "11                         0.3      0.5                3 2016-12-26 14:48:28   \n",
       "12                         0.3      0.5                1 2016-12-26 14:48:29   \n",
       "13                         0.3      0.5                1 2016-12-26 14:48:29   \n",
       "14                         0.3      0.5                1 2016-12-26 14:48:29   \n",
       "15                         0.3      0.5                2 2016-12-26 14:48:29   \n",
       "16                         0.3      0.5                1 2016-12-26 14:48:29   \n",
       "17                         0.3      0.5                2 2016-12-26 14:48:29   \n",
       "18                         0.3      0.5                1 2016-12-26 14:48:29   \n",
       "19                         0.3      0.5                3 2016-12-26 14:48:29   \n",
       "20                         0.3      0.5                1 2016-12-26 14:48:30   \n",
       "21                         0.3      0.5                1 2016-12-26 14:48:30   \n",
       "22                         0.3      0.5                4 2016-12-26 14:48:30   \n",
       "23                         0.3      0.5                5 2016-12-26 14:48:30   \n",
       "24                         0.3      0.5                1 2016-12-26 14:48:30   \n",
       "25                         0.3      0.5                2 2016-12-26 14:48:30   \n",
       "26                         0.3      0.5                3 2016-12-26 14:48:30   \n",
       "27                         0.3      0.5                1 2016-12-26 14:48:30   \n",
       "28                         0.3      0.5                2 2016-12-26 14:48:30   \n",
       "29                         0.3      0.5                4 2016-12-26 14:48:30   \n",
       "...                        ...      ...              ...                 ...   \n",
       "1930560                    0.3      0.5                1 2016-12-31 23:59:34   \n",
       "1930561                    0.3      0.5                2 2016-12-31 23:59:36   \n",
       "1930562                    0.3      0.5                1 2016-12-31 23:59:37   \n",
       "1930563                    0.3      0.5                1 2016-12-31 23:59:37   \n",
       "1930564                    0.3      0.5                1 2016-12-31 23:59:37   \n",
       "1930565                    0.3      0.5                1 2016-12-31 23:59:37   \n",
       "1930566                    0.3      0.5                1 2016-12-31 23:59:38   \n",
       "1930567                    0.3      0.5                1 2016-12-31 23:59:39   \n",
       "1930568                    0.3      0.5                1 2016-12-31 23:59:42   \n",
       "1930569                    0.3      0.5                1 2016-12-31 23:59:42   \n",
       "1930570                    0.3      0.5                1 2016-12-31 23:59:42   \n",
       "1930571                    0.3      0.5                1 2016-12-31 23:59:43   \n",
       "1930572                    0.3      0.5                1 2016-12-31 23:59:44   \n",
       "1930573                    0.3      0.5                1 2016-12-31 23:59:44   \n",
       "1930574                    0.3      0.5                4 2016-12-31 23:59:44   \n",
       "1930575                    0.3      0.5                1 2016-12-31 23:59:45   \n",
       "1930576                    0.3      0.5                1 2016-12-31 23:59:46   \n",
       "1930577                    0.3      0.5                1 2016-12-31 23:59:46   \n",
       "1930578                    0.3      0.5                6 2016-12-31 23:59:46   \n",
       "1930579                    0.3      0.5                1 2016-12-31 23:59:48   \n",
       "1930580                    0.3      0.5                2 2016-12-31 23:59:50   \n",
       "1930581                    0.3      0.5                1 2016-12-31 23:59:53   \n",
       "1930582                    0.3      0.5                1 2016-12-31 23:59:53   \n",
       "1930583                    0.3      0.5                1 2016-12-31 23:59:53   \n",
       "1930584                    0.3      0.5                1 2016-12-31 23:59:53   \n",
       "1930585                    0.3      0.5                1 2016-12-31 23:59:54   \n",
       "1930586                    0.3      0.5                1 2016-12-31 23:59:54   \n",
       "1930587                    0.3      0.5                2 2016-12-31 23:59:54   \n",
       "1930588                    0.3      0.5                1 2016-12-31 23:59:54   \n",
       "1930589                    0.3      0.5                1 2016-12-31 23:59:58   \n",
       "\n",
       "         pickup_location_id  rate_code_id  tip_amount  tolls_amount  \\\n",
       "0                     186.0             1        1.00           0.0   \n",
       "1                     141.0             1        2.82           0.0   \n",
       "2                      79.0             1        0.00           0.0   \n",
       "3                     234.0             1        0.00           0.0   \n",
       "4                     170.0             1        1.76           0.0   \n",
       "5                     164.0             1        1.96           0.0   \n",
       "6                     230.0             1        0.00           0.0   \n",
       "7                      82.0             1        0.00           0.0   \n",
       "8                     237.0             1        1.17           0.0   \n",
       "9                     237.0             1        2.00           0.0   \n",
       "10                     48.0             1        1.74           0.0   \n",
       "11                    141.0             1        3.00           0.0   \n",
       "12                    239.0             1        2.00           0.0   \n",
       "13                     90.0             1        0.00           0.0   \n",
       "14                    236.0             1        2.70           0.0   \n",
       "15                    233.0             1        1.95           0.0   \n",
       "16                    236.0             1        0.00           0.0   \n",
       "17                    237.0             1        1.16           0.0   \n",
       "18                    142.0             1        0.00           0.0   \n",
       "19                    230.0             1        2.16           0.0   \n",
       "20                    237.0             1        1.05           0.0   \n",
       "21                    137.0             1        1.00           0.0   \n",
       "22                    239.0             1        1.55           0.0   \n",
       "23                    239.0             1        1.26           0.0   \n",
       "24                    164.0             1        0.00           0.0   \n",
       "25                    142.0             1        1.86           0.0   \n",
       "26                     48.0             1        0.00           0.0   \n",
       "27                    186.0             1        0.00           0.0   \n",
       "28                    163.0             1        0.00           0.0   \n",
       "29                    231.0             1        1.32           0.0   \n",
       "...                     ...           ...         ...           ...   \n",
       "1930560               138.0             1        9.80           0.0   \n",
       "1930561               231.0             1        0.00           0.0   \n",
       "1930562               236.0             1        0.00           0.0   \n",
       "1930563               213.0             1       14.06           0.0   \n",
       "1930564                87.0             1        0.00           0.0   \n",
       "1930565               234.0             1        1.84           0.0   \n",
       "1930566                43.0             1        0.00           0.0   \n",
       "1930567                68.0             1        4.86           0.0   \n",
       "1930568               161.0             1        3.15           0.0   \n",
       "1930569               162.0             1        0.00           0.0   \n",
       "1930570               107.0             1        0.00           0.0   \n",
       "1930571                79.0             1        0.00           0.0   \n",
       "1930572                68.0             1        1.00           0.0   \n",
       "1930573                40.0             1        4.95           0.0   \n",
       "1930574               234.0             1        2.94           0.0   \n",
       "1930575               237.0             1        0.00           0.0   \n",
       "1930576                79.0             1        0.00           0.0   \n",
       "1930577                75.0             1        3.86           0.0   \n",
       "1930578               262.0             1        3.25           0.0   \n",
       "1930579                75.0             1        0.00           0.0   \n",
       "1930580               209.0             1        0.00           0.0   \n",
       "1930581               132.0             1        9.05           0.0   \n",
       "1930582               234.0             1        0.00           0.0   \n",
       "1930583               144.0             1        0.00           0.0   \n",
       "1930584               138.0             1        0.00           0.0   \n",
       "1930585                68.0             1        0.00           0.0   \n",
       "1930586               166.0             1        0.00           0.0   \n",
       "1930587                68.0             1        0.00           0.0   \n",
       "1930588               236.0             1        0.00           0.0   \n",
       "1930589               144.0             1        0.00           0.0   \n",
       "\n",
       "         total_amount  trip_distance trip_type pickup_ct_id pickup_tz_id  \n",
       "0               15.30           3.10    yellow          NaN          NaN  \n",
       "1               14.12           2.06    yellow          NaN          NaN  \n",
       "2               15.80           4.26    yellow          NaN          NaN  \n",
       "3                4.30           0.30    yellow          NaN          NaN  \n",
       "4               10.56           1.29    yellow          NaN          NaN  \n",
       "5               11.76           0.88    yellow          NaN          NaN  \n",
       "6               19.30           4.64    yellow          NaN          NaN  \n",
       "7                5.80           0.70    yellow          NaN          NaN  \n",
       "8                8.97           1.20    yellow          NaN          NaN  \n",
       "9               12.30           1.40    yellow          NaN          NaN  \n",
       "10               7.54           0.92    yellow          NaN          NaN  \n",
       "11               7.80           0.33    yellow          NaN          NaN  \n",
       "12              13.30           2.00    yellow          NaN          NaN  \n",
       "13               5.30           0.50    yellow          NaN          NaN  \n",
       "14              13.50           1.80    yellow          NaN          NaN  \n",
       "15              11.75           0.40    yellow          NaN          NaN  \n",
       "16              11.80           1.80    yellow          NaN          NaN  \n",
       "17               6.96           0.53    yellow          NaN          NaN  \n",
       "18               7.30           1.28    yellow          NaN          NaN  \n",
       "19              12.96           1.10    yellow          NaN          NaN  \n",
       "20               6.35           0.80    yellow          NaN          NaN  \n",
       "21               7.80           0.70    yellow          NaN          NaN  \n",
       "22              11.85           2.00    yellow          NaN          NaN  \n",
       "23               7.56           0.73    yellow          NaN          NaN  \n",
       "24              11.30           2.08    yellow          NaN          NaN  \n",
       "25              11.16           1.81    yellow          NaN          NaN  \n",
       "26              10.30           1.01    yellow          NaN          NaN  \n",
       "27               3.80           0.34    yellow          NaN          NaN  \n",
       "28              11.80           1.54    yellow          NaN          NaN  \n",
       "29               6.62           0.59    yellow          NaN          NaN  \n",
       "...               ...            ...       ...          ...          ...  \n",
       "1930560         42.60          10.70    yellow          NaN          NaN  \n",
       "1930561         22.30           6.50    yellow          NaN          NaN  \n",
       "1930562         13.80           3.90    yellow          NaN          NaN  \n",
       "1930563         36.36           6.80    yellow          NaN          NaN  \n",
       "1930564         33.30          11.61    yellow          NaN          NaN  \n",
       "1930565         14.14           1.97    yellow          NaN          NaN  \n",
       "1930566         25.80           7.53    yellow          NaN          NaN  \n",
       "1930567         29.16           4.23    yellow          NaN          NaN  \n",
       "1930568         18.95           1.50    yellow          NaN          NaN  \n",
       "1930569          5.30           0.40    yellow          NaN          NaN  \n",
       "1930570         10.30           1.57    yellow          NaN          NaN  \n",
       "1930571         11.80           1.50    yellow          NaN          NaN  \n",
       "1930572          8.30           1.40    yellow          NaN          NaN  \n",
       "1930573         24.75           5.30    yellow          NaN          NaN  \n",
       "1930574         12.74           1.42    yellow          NaN          NaN  \n",
       "1930575          3.80           0.04    yellow          NaN          NaN  \n",
       "1930576         17.80           4.70    yellow          NaN          NaN  \n",
       "1930577         23.16           5.13    yellow          NaN          NaN  \n",
       "1930578         24.55           6.56    yellow          NaN          NaN  \n",
       "1930579          7.30           1.10    yellow          NaN          NaN  \n",
       "1930580         28.30           9.50    yellow          NaN          NaN  \n",
       "1930581         54.35          15.90    yellow          NaN          NaN  \n",
       "1930582          7.30           0.22    yellow          NaN          NaN  \n",
       "1930583         25.80           6.69    yellow          NaN          NaN  \n",
       "1930584         11.80           3.18    yellow          NaN          NaN  \n",
       "1930585         32.80          11.10    yellow          NaN          NaN  \n",
       "1930586          7.30           1.14    yellow          NaN          NaN  \n",
       "1930587          8.80           0.97    yellow          NaN          NaN  \n",
       "1930588         11.30           2.42    yellow          NaN          NaN  \n",
       "1930589          6.30           0.70    yellow          NaN          NaN  \n",
       "\n",
       "[1930590 rows x 17 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# zz = (trips1.merge(df1, left_index=True, right_on='trip_id', how='left')).merge(\n",
    "#     df2, left_index=True, right_on='trip_id', how='left')\n",
    "zz.drop([ 'vendor_id', 'store_and_fwd_flag', 'payment_type'], axis=1).sort_index().rename(\n",
    "    columns={'census_tract_id': 'pickup_ct_id', 'taxi_zone_id': 'pickup_tz_id'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x7f0826d5a828>"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.connect().execute('drop table {}; drop table {};'.format(tableID, tableIDLoc))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def assign_taxi_zones(df, lon_var, lat_var, locid_var):\n",
    "    \"\"\"Joins DataFrame with Taxi Zones shapefile.\n",
    "\n",
    "    This function takes longitude values provided by `lon_var`, and latitude\n",
    "    values provided by `lat_var` in DataFrame `df`, and performs a spatial join\n",
    "    with the NYC taxi_zones shapefile. \n",
    "\n",
    "    The shapefile is hard coded in, as this function makes a hard assumption of\n",
    "    latitude and longitude coordinates. It also assumes latitude=0 and \n",
    "    longitude=0 is not a datapoint that can exist in your dataset. Which is \n",
    "    reasonable for a dataset of New York, but bad for a global dataset.\n",
    "\n",
    "    Only rows where `df.lon_var`, `df.lat_var` are reasonably near New York,\n",
    "    and `df.locid_var` is set to np.nan are updated. \n",
    "\n",
    "    Parameters\n",
    "    ----------\n",
    "    df : pandas.DataFrame or dask.DataFrame\n",
    "        DataFrame containing latitudes, longitudes, and location_id columns.\n",
    "    lon_var : string\n",
    "        Name of column in `df` containing longitude values. Invalid values \n",
    "        should be np.nan.\n",
    "    lat_var : string\n",
    "        Name of column in `df` containing latitude values. Invalid values \n",
    "        should be np.nan\n",
    "    locid_var : string\n",
    "        Name of column in `df` containing taxi_zone location ids. Rows with\n",
    "        valid, nonzero values are not overwritten. \n",
    "    \"\"\"\n",
    "\n",
    "    localdf = df[[lon_var, lat_var, locid_var]].copy()\n",
    "    # localdf = localdf.reset_index()\n",
    "    localdf[lon_var] = localdf[lon_var].fillna(value=0.)\n",
    "    localdf[lat_var] = localdf[lat_var].fillna(value=0.)\n",
    "    localdf['replace_locid'] = (localdf[locid_var].isnull()\n",
    "                                & (localdf[lon_var] != 0.)\n",
    "                                & (localdf[lat_var] != 0.))\n",
    "    \n",
    "    \n",
    "    import sqlalchemy, uuid, os\n",
    "    engine = sqlalchemy.create_engine(open(os.path.expanduser('~/.sqlconninfo')).read())\n",
    "    \n",
    "    tableID = 'uu{}'.format(uuid.uuid1().hex)\n",
    "    localdf.to_sql(tableID, engine)\n",
    "    \n",
    "    conn = engine.connect()\n",
    "    \n",
    "    conn.execute()\n",
    "\n",
    "#     if (np.any(localdf['replace_locid'])):\n",
    "#         shape_df = geopandas.read_file('../shapefiles/taxi_zones_latlon.shp')\n",
    "#         shape_df.drop(['OBJECTID', \"Shape_Area\", \"Shape_Leng\", \"borough\", \"zone\"],\n",
    "#                       axis=1, inplace=True)\n",
    "\n",
    "#         try:\n",
    "#             local_gdf = geopandas.GeoDataFrame(\n",
    "#                 localdf, crs={'init': 'epsg:4326'},\n",
    "#                 geometry=[Point(xy) for xy in\n",
    "#                           zip(localdf[lon_var], localdf[lat_var])])\n",
    "\n",
    "#             local_gdf = geopandas.sjoin(\n",
    "#                 local_gdf, shape_df, how='left', op='intersects')\n",
    "\n",
    "#             # one point can intersect more than one zone -- for example if on\n",
    "#             # the boundary between two zones. Deduplicate by taking first valid.\n",
    "#             local_gdf = local_gdf[~local_gdf.index.duplicated(keep='first')]\n",
    "\n",
    "#             local_gdf.LocationID.values[~local_gdf.replace_locid] = (\n",
    "#                 (local_gdf[locid_var])[~local_gdf.replace_locid]).values\n",
    "\n",
    "#             return local_gdf.LocationID.rename(locid_var).astype(np.float64)\n",
    "#         except ValueError as ve:\n",
    "#             print(ve)\n",
    "#             print(ve.stacktrace())\n",
    "#             return df[locid_var]\n",
    "#     else:\n",
    "#         return df[locid_var]"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [default]",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
